SELECT 
        sale.id, 
        sale.code, 
        CONCAT('[', JSON_OBJECT('id', CONCAT(sale.customer_id), 'displayText', CONCAT(customer.name, ' - Credit Limit: $' ,customer.credit_limit)), ']') AS customer_id, 
        DATE_FORMAT(sale.sale_date, '%m/%d/%Y') as sale_date_td, 
        DATE_FORMAT(sale.due_date, '%m/%d/%Y') as due_date_td, 
        sale.sale_date, 
        sale.due_date, 
        sale.email,
        FORMAT(svi_sum.total_qty, 0) AS qty,
        '' AS send_email,
        sale.bill_to_address, 
        sale.bill_to_apt, 
        sale.bill_to_city, 
        sale.bill_to_state, 
        sale.bill_to_zip, 
        sale.ship_to_address, 
        sale.ship_to_apt, 
        sale.ship_to_city, 
        sale.ship_to_state, 
        sale.ship_to_zip, 
        sale.ship_via, 
        sale.shipping_date, 
        sale.shipping_time, 
        sale.shipping_status, 
        sale.delivered_date, 
        sale.tracking_number, 
        sale.notes,
        sale.packslip_notes,
        sale.sale_statement,
        sale.sale_footer,
        sale.payment_status,
        sale.terms,
        sale.spent_credit,
        sale.printed_note,
        COALESCE(sale.packing_slip_last_printed,'') as packing_slip_last_printed,
        COALESCE(TIME(sale.packing_slip_last_printed),'00:00:00') as packing_slip_last_printed_time,
        CONCAT(DATEDIFF(sale.due_date, CURDATE())) AS remaining_days,
        customer.code as code_customer,
        customer.name,
        customer.avatar as customerAvatar,
        customer.avatarColor as customerAvatarColor,
        customer.avatarColorLetter as customerAvatarColorLetter,
        customer.avatarLetter AS customerAvatarLetter,
        customer.phone,
        CONCAT(sale.grand_total) as grand_total,
        CONCAT(sale.total_profit_pct) as total_profit_pct,
        CONCAT('$', FORMAT((sale.grand_total - sale.credit_amount),2)) as grand_total_dt,
        CONCAT(
            '[',
                
                '{',
                    '"data":[',
                                  GROUP_CONCAT(
                                        DISTINCT(
                                        JSON_OBJECT(
                                            'id',CONCAT(sale_vs_item.id),
                                            'qty_available', CONCAT(sale_vs_item.qty)
                                                )

                                        ) SEPARATOR ','),
                            ']',
                '}',  
            
            ']'
            ) AS inventory_stock,
        CONCAT(
            '[',
                
                '{',
                    '"tableName":"entitychanges"', 
                    ',"data":[',
                                  GROUP_CONCAT(
                                        DISTINCT(
                                        JSON_OBJECT(
                                            'id',   CONCAT(entityAudit.id), 
                                            'user_name',CONCAT(AES_DECRYPT(UNHEX(entityAudit.__name), 'uValKP2n94W8200cq'), ' ', entityAudit.last_name),
                                            'date_change',DATE_FORMAT(entityAudit.date_registered, '%m/%d/%Y %H:%i:%s'),                       
                                            'avatar',CONCAT(entityAudit.avatar),               
                                            'changes',CONCAT(entityAudit.changes)          
                                                )

                                        ) SEPARATOR ','),
                            ']',
                '}',  
            
            ']'
        ) AS entitychanges,

        CONCAT(
            '[',
                
                '{',
                    '"tableName":"sale_vs_item"', 
                    ',"data":[',
                                  GROUP_CONCAT(
                                        DISTINCT(
                                        JSON_OBJECT(
                                            'id',CONCAT(sale_vs_item.id),
                                            
                                            'purchase_vs_itemId',
                                            
                                                CONCAT('[', 
                                                            JSON_OBJECT(
                                                                        'id',   CONCAT(sale_vs_item.purchase_vs_itemId), 
                                                                        'displayText', CONCAT(purchase.code ,' - ',item.name,' - ',  
                                                                         ROUND(( (sale_vs_item.qty - sale_vs_item.returned_qty) + (purchase_vs_item.qty- (purchase_vs_item.sold_qty + purchase_vs_item.returned_qty + purchase_vs_item.lost_qty )))),
                                                                            ' - ',COALESCE(DATE_FORMAT(purchase.delivered_date,'%M %e, %Y'), DATE_FORMAT(CURRENT_DATE(),'%M %e, %Y')) ),
                                                                        'otherField', JSON_OBJECT('selling_price', purchase_vs_item.selling_price,
                                                                                                    'item_name', item.name,
                                                                                                    'po', purchase.code,
                                                                                                    'cost', purchase_vs_item.unit_price + (IF(purchase_vs_item.qty != 0, (purchase_vs_item.cogs_row_amount / (purchase_vs_item.qty - purchase_vs_item.returned_qty)), 0.00)),                                                                                                                                                               
                                                                                                    'old_qty', sale_vs_item.qty ,                                                                                                                                                               
                                                                                                    'old_returnQty', sale_vs_item.returned_qty ,                                                                                                                                                               
                                                                                                    'sale_vs_itemId', sale_vs_item.id ,                                                                                                                                                               
                                                                                                    'qty_available', ( (sale_vs_item.qty - sale_vs_item.returned_qty) + (purchase_vs_item.qty- (purchase_vs_item.sold_qty + purchase_vs_item.returned_qty + purchase_vs_item.lost_qty ))))
                                                                            
                                                                        ),
                                                        ']'),  

                                           
                                            'selling_type', sale_vs_item.selling_type,
                                            'qty', CONCAT(ROUND(sale_vs_item.qty)), 
                                            'returned_qty', CONCAT(sale_vs_item.returned_qty),
                                            'cost', CONCAT(purchase_vs_item.unit_price + (IF(purchase_vs_item.qty != 0, (purchase_vs_item.cogs_row_amount / (purchase_vs_item.qty - purchase_vs_item.returned_qty)), 0.00))),
                                            'cogs_row_amount', CONCAT(sale_vs_item.cogs_row_amount),
                                            'price', CONCAT(sale_vs_item.price),                           
                                            'total', CONCAT(sale_vs_item.total)                           
                                                )

                                        ) SEPARATOR ','),
                            ']',
                '},',  
                '{',
                '"tableName":"sale_vs_return"', 
                ',"data":[',
                                GROUP_CONCAT(
                                DISTINCT(
                                JSON_OBJECT(
                                    'id',CONCAT(saleVslost.id),
                                    'sale_vs_itemId', CONCAT(saleVslost.sale_vs_itemId),
                                    'lost_qty', CONCAT(saleVslost.lost_qty),
                                    'lost_price', CONCAT(saleVslost.lost_price),
                                    'date', saleVslost.date,
                                    'notes', CONCAT(saleVslost.notes)
                                )) SEPARATOR ','),
                          ']',
            '}',
            
            ']'

            
        ) AS multiTables



        FROM sale
        LEFT JOIN sale_vs_item ON sale_vs_item.saleId = sale.id AND sale_vs_item.deleted = '0'
        LEFT JOIN purchase_vs_item ON sale_vs_item.purchase_vs_itemId = purchase_vs_item.id 
        LEFT JOIN item ON purchase_vs_item.item_id = item.id 
        LEFT JOIN purchase ON purchase.id = purchase_vs_item.purchaseId 
        LEFT JOIN customer ON customer.id = sale.customer_id
        LEFT JOIN (
            SELECT 
                sale_vs_return.id AS id, 
                sale_vs_return.saleId AS saleId, 
                CONCAT('[', 
                    JSON_OBJECT(
                                'id',   CONCAT(sale_vs_return.sale_vs_itemId), 
                                'displayText', CONCAT(item.name,' - ',  sale_vs_item.selling_type),
                                'otherField', JSON_OBJECT('selling_price', sale_vs_item.price,
                                                            'item_name', item.name,
                                                            'po', sale.code,  
                                                            'sale_vs_itemId',   CONCAT(sale_vs_item.id),                                                                               
                                                            'item_unit_cost', CONCAT(sale_vs_item.price),                                                                                
                                                            'qty_available', CONCAT(sale_vs_item.qty))
                                    
                                ),
                ']') as sale_vs_itemId, 
                sale_vs_return.date AS date,
                sale_vs_return.lost_qty,
                sale_vs_return.notes,
                sale_vs_return.lost_price

            FROM 
                sale_vs_return 
                INNER JOIN sale on sale.id = sale_vs_return.saleId and sale.deleted = '0'
                INNER JOIN sale_vs_item on sale_vs_item.id = sale_vs_return.sale_vs_itemId and sale_vs_item.deleted = '0'
                LEFT JOIN purchase_vs_item ON sale_vs_item.purchase_vs_itemId = purchase_vs_item.id
                LEFT JOIN item ON purchase_vs_item.item_id = item.id 
                LEFT JOIN item_unit ON item_unit.id = sale_vs_item.selling_type 
            WHERE 
                sale_vs_return.deleted = '0' 
                AND sale_vs_return.saleId = '14477'
        ) AS saleVslost ON saleVslost.saleId = sale.id
        left join (
        	select 
        		entity_audit.id,
	        	entity_audit.entity_id,
        		a.__name,
	        	a.last_name,
	        	entity_audit.date_registered,
	        	a.avatar,
	        	entity_audit.changes
        	from  
	        	entity_audit
	        	LEFT JOIN users a ON a.id = entity_audit.user_id
        	where 
        		entity_audit.entity_id  = '14477' AND entity_audit.entity = 'sale'
        ) as entityAudit on entityAudit.entity_id = sale.id 

        LEFT JOIN (
		    SELECT saleId, SUM(qty) AS total_qty
		    FROM sale_vs_item
		    WHERE deleted = '0' and sale_vs_item.saleId = '14477'
		    GROUP BY saleId
		) svi_sum ON svi_sum.saleId = sale.id


        WHERE sale.deleted = '0' AND sale.id = '14477';